package com.mobiusws.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.mobiusws.reports.model.DashboardReportInfo;
//import com.mobiusws.util.DateUtils;

public class DashboardUtil {

	private static DocumentBuilder documentBuilder;
	private static Document document;

	static {
		try {
			documentBuilder = DocumentBuilderFactory.newInstance()
					.newDocumentBuilder();
		} catch (ParserConfigurationException e) {
			e.printStackTrace();
		}
	}

	private static Document getDocument() throws Exception {
		Document doc = null;
		InputStream in = DashboardUtil.class
				.getResourceAsStream("/com/mobiusws/resources/dashboard.xml");
		doc = documentBuilder.parse(in);
		in.close();
		return doc;
	};

	private static Connection GetDBConnection() {
		Context ctx = null;
		Connection conn = null;
		try {
			ctx = new InitialContext();
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/dbxDataSource");
			conn = ds.getConnection();
		} catch (SQLException e) {
			e.getMessage();
		} catch (NamingException e) {
			e.getMessage();
		} finally {
			if (conn == null) {
				System.out
						.println("Didn't get connection, which probably means that no Driver accepted the URL");
			} else {

			}
		}

		return conn;
	}

	private static Connection GetCoreDBConnection() {
		Context ctx = null;
		Connection conn = null;
		try {
			ctx = new InitialContext();
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/coreDataSource");
			conn = ds.getConnection();
		} catch (SQLException e) {
			e.getMessage();
		} catch (NamingException e) {
			e.getMessage();
		} finally {
			if (conn == null) {
				System.out
						.println("Didn't get connection, which probably means that no Driver accepted the URL");
			} else {

			}
		}

		return conn;
	}

	public static List<DashboardReportInfo> getDashboardReportsInfo()
			throws Exception {
		List<DashboardReportInfo> infos = new LinkedList<DashboardReportInfo>();
		document = getDocument();
		NodeList nodeList = document.getElementsByTagName("report");
		// System.out.println(nodeList.getLength());
		for (int i = 0; i < nodeList.getLength(); i++) {
			Node node = nodeList.item(i);
			NamedNodeMap attributes = node.getAttributes();
			String table = attributes.getNamedItem("table").getNodeValue();
			String name = attributes.getNamedItem("name").getNodeValue();
			String id = attributes.getNamedItem("id").getNodeValue();
			DashboardReportInfo info = new DashboardReportInfo(table, name, id);
			infos.add(info);
		}
		return infos;
	}

	private static List<String> getDays(String monthStr) throws Exception {
		List<String> days = new LinkedList<String>();
		// get the past 30 days
		final int dateNum=30;
		for (int i = dateNum; i > 0; i--) {
			Calendar cal = Calendar.getInstance();
			cal.add(Calendar.DATE, -i);
			// String day = new SimpleDateFormat(
			// "yyyy-MM-dd ").format(cal.getTime());
			String day = DateUtil.date2String(DateUtil.DAY_FORMAT, cal.getTime());
			days.add(day);
		}
		// String sql =
		// "select distinct DATE_FORMAT(traffic_date,'%Y-%m-%d') as traffic_date from dashboard_summary where traffic_date >  DATE_ADD(now(), interval -30 day)";
		// Connection conn = null;
		// try {
		// conn = GetDBConnection();
		// Statement stmt = conn.createStatement();
		// ResultSet rs = stmt.executeQuery(sql);
		// while (rs.next()) {
		// days.add(rs.getString("traffic_date"));
		// }
		// stmt.close();
		// rs.close();
		// } finally {
		// if (conn != null) {
		// conn.close();
		// }
		// }
		return days;
	}

	public static Map<String, Integer> getMap(String monthStr) throws Exception {
		Connection conn = null;
		Map<String, Integer> map = new LinkedHashMap<String, Integer>();
		String sql = "select DATE_FORMAT(traffic_date,'%Y-%m-%d') as traffic_date, report_name, record_count from dashboard_summary where traffic_date >  DATE_ADD(now(), interval -30 day)";
		try {
			conn = GetDBConnection();

			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String key = rs.getString("traffic_date")
						+ rs.getString("report_name");
				int value = rs.getInt("record_count");
				map.put(key, value);
				// System.out.println(key + ", " + value);
			}
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return map;
	}

	public static String getJson() throws Exception {
		StringBuilder jsonBld = new StringBuilder();
		jsonBld.append("[");
		String monthStr = DateUtil.date2String(DateUtil.MONTH_FORMAT, new Date());
		List<DashboardReportInfo> infos = getDashboardReportsInfo();
		List<String> days = getDays(monthStr);
		Map<String, Integer> map = getMap(monthStr);
		for (String day : days) {
			// BEGIN ADD BY Wilson 2012-01-04
			// if (isAllReportEmpty(day, map, infos)) {
			// continue;
			// }
			// END ADD BY Wilson 2012-01-04
			jsonBld.append("[").append("\'").append(day).append("\',");
			for (DashboardReportInfo info : infos) {
				String key = day + info.getTable();
				int value = (map.get(key) == null ? 0 : map.get(key));
				jsonBld.append(value).append(",");
			}
			jsonBld.deleteCharAt(jsonBld.length() - 1);
			jsonBld.append("],");
		}
		jsonBld.deleteCharAt(jsonBld.length() - 1);
		jsonBld.append("]");
		// System.out.println(jsonBld.toString());
		return jsonBld.toString();
	}

	public static void main(String[] args) throws Exception {
		List<DashboardReportInfo> list = getDashboardReportsInfo();
		for (DashboardReportInfo info : list) {
			System.out.printf("table=%s, name=%s, id=%s", info.getTable(),
					info.getName(), info.getId());
			System.out.println();
		}
	}

}
